package DBLayer;
import ModelLayer.Supplier;
import java.sql.*;
import java.util.ArrayList;


public class DBSupplier implements IFDBSupplier
{
    private  Connection con;
    
    public DBSupplier() 
    {
      con = DBConnection.getInstance().getDBcon();
    }
  
    
    @Override
    public ArrayList getAllSuppliers(boolean retriveAssociation)
    {
        ArrayList list = new ArrayList();
        list = miscWhere("", retriveAssociation);
        return list;
    }
    
    
    @Override
    public Supplier findSupplier(int supplierID, boolean retriveAssociation)
    {   
        String wClause = "  suId = '" + supplierID + "'";
        
        return singleWhere(wClause, retriveAssociation);
    }
    
  
    @Override
    public Supplier searchSupplierName(String name, boolean retriveAssociation)
    {
        String wClause = "name like '%" + name + "%'";

        return singleWhere(wClause, retriveAssociation);
    }
    
    @Override
    public Supplier searchSupplierID(int supplierID, boolean retriveAssociation)
    {
        String wClause = "suId like '%" + supplierID + "%'";

        return singleWhere(wClause, retriveAssociation);
    }
 
    @Override
    public void insertSupplier(Supplier sup)
    {  
       
       String query = "INSERT INTO Supplier(name, address, phoneNo)"
               + "VALUES(" +
               "'"+ sup.getName()+ "', "  +
               "'"+ sup.getAddress()+ "',"  +
               " "+ sup.getPhoneNo()+") ";
       
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            
            
            stmt.close();
        }
        catch(Exception e)
        {
            
        }
    }

    @Override
    public void updateSupplier(Supplier sup)
    {
        Supplier supObj  = sup;

        String query="UPDATE Supplier SET "+
                  "name ='"+ supObj.getName()+"', "+
                  "address ='"+ supObj.getAddress() + "', " +
                  "phoneNo ='"+ supObj.getPhoneNo() + "' " +
                  "WHERE suId = '"+ supObj.getSupID() + "';";
        
        System.out.println("Update query:" + query);
        
        try
        {
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                stmt.executeUpdate(query);

                stmt.close();
        }
        catch(Exception ex)
        {
            
        }
    }
	
    @Override
    public void deleteSupplier(int supplierID)
    {
        String query="DELETE FROM supplier WHERE suId = '" +
                        supplierID + "'";

        try
        { 
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                stmt.executeUpdate(query);

                stmt.close();
        }	
        catch(Exception ex)
        {

        }
    }
    
    public ArrayList<Supplier> searchSupplierNames(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        
        return miscWhere(wClause, retriveAssociation);
    }
	
    private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Supplier> list = new ArrayList<Supplier>();	

        String query =  buildQuery(wClause);

        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next())
            {
                Supplier supObj = new Supplier();
                supObj = buildSupplier(results);	
                list.add(supObj);	
            }
            
            stmt.close(); 
        }	
        catch(Exception e)
        {

        }
        
        return list;
    }
	
    private Supplier singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Supplier supObj = new Supplier();

        String query =  buildQuery(wClause);

        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next())
            {
                supObj = buildSupplier(results);

                stmt.close();
            }
            else
            {
                supObj = null;
            }
        }	
        catch(Exception e)
        {

        }

        return supObj;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT suId, name, address, phoneNo FROM supplier";

        if (wClause.length()>0)
        {
            query=query+" WHERE "+ wClause;
        }

        return query;
    }
    
    private Supplier buildSupplier(ResultSet results)
    {   
        Supplier supObj = new Supplier();
        
        try
        { 
            supObj.setSupID(results.getInt("suId"));
            supObj.setName(results.getString("name"));
            supObj.setAddress(results.getString("address"));
            supObj.setPhoneNo(results.getInt("phoneNo"));
        }
        catch(Exception e)
        {

        }
        
        return supObj;
    }
    
    @Override
    public int MaxID()
    {
        String query = "SELECT max(suId) FROM Supplier";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading max id: " + e);
        } 
        return id;     
    }
}
